'''
Created on 2011-12-11

@author: jiangxd
'''
import sqlite3
import time

conn = sqlite3.connect('words.sqlite3')

def add_word(word, explain, phonetic):
	ts = time.time()
	c = conn.cursor()
#	c.execute("select time from last_words where word=? limit 1" , (word,))
#	try:
#		c.next()
#		c.execute("update last_words set time=?, dictated=1", (ts,))
#	except StopIteration:
	c.execute("insert into last_words (word,explain,phonetic,time,dictated) values(?,?,?,?,1)", (word, explain, phonetic, ts) )
	conn.commit()

def update_dictated_status(wordobj):
	ts = time.time()
	c = conn.cursor()
	c.execute("update last_words set time=?, dictated=1 where word=?", (ts, wordobj['word']) )
	conn.commit()

def get_dictated_word_at_reverse(index):
	c = conn.cursor()
	c.execute("select * from last_words where dictated=1 order by time desc limit 1 offset ?", (index,))
	row = c.fetchone()
	if row is None: return None
	return {'word':row[0],'explain':row[1],'phonetic':row[2],'time':row[3]}

def get_word(word):
	c = conn.cursor()
	c.execute("select * from last_words where word=?", (word,))
	row = c.fetchone()
	if row is None: return None
	return {'word':row[0],'explain':row[1],'phonetic':row[2],'time':row[3]}
#	
#def list_dictated_words(limit,offset):
#	c = conn.cursor()
#	c.execute("select word from last_words where dictated=1 limit ? offset ?", (limit, offset) )
#	words = []
#	for row in c:
#		words.append(row.word)
#	return words

def clean_words():
	c = conn.cursor()
	c.execute("update last_words set dictated=0")
	conn.commit()

def remove_dictated_word(word):
	c = conn.cursor()
	c.execute("update last_words set dictated=0 where word=?", (word,) )
	conn.commit()

def get_dictated_size():
	c = conn.cursor()
	c.execute("select count(*) from last_words where dictated=1")
	return c.fetchone()[0]